﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


/// <summary>
/// 文件名：ClsCustPriceDAL.cs
/// 作  者：Kidy
/// 时  间：2006.12.01
/// 说  明：ClsCustPriceDAL类用于客户价格信息相关处理
/// </summary>

public class ClsCustPriceDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取所有客户价格主表信息
    /// </summary>
    /// <param name="Co_ID">公司ID</param>
    /// <param name="User_ID">用户ID</param>
    /// <param name="Ds_ID">数据集ID</param>
    /// <returns>返回客户价格主表信息数据集</returns>
    public DataSet GetCustPriceMains(string Co_ID, string User_ID, string Ds_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_getCustItemDate", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        //dat.SelectCommand.Parameters.Add("@co_id", Co_ID);
        //dat.SelectCommand.Parameters.Add("@user_id", User_ID);
        //dat.SelectCommand.Parameters.Add("@ds_id", Ds_ID);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取所有客户价格从表信息
    /// <param name="Tm_On">盘点单号</param>
    /// </summary>
    /// <returns>返回客户价格从表信息数据集</returns>
    public DataSet GetCustPriceChilds(string sPm_No)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustPriceChilds", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@Pm_no", sPm_No);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 增加客户价格信息
    /// </summary>
    /// <param>客户价格编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    /// strCt_no, strCr_id, strDm_Writer, strCr_Remark
    public Boolean AddCustPrice(string sCt_no,string sCr_id,string sOper, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddCustPrice", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Ct_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Ct_no"].Value = sCt_no;
        com.Parameters.Add("@Cr_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Cr_id"].Value = sCr_id;
        com.Parameters.Add("@Oper", SqlDbType.NVarChar, 20);
        com.Parameters["@Oper"].Value = sOper;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }    

    /// <summary>
    /// 增加客户价格明细信息
    /// </summary>
    /// <param>客户价格编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    /// strPm_No, strProductId, dChildPrice1, dChildPrice2,dChildPrice3,strChildRemark
    public Boolean AddChildCustPrice(string sPm_no, string sIm_id, decimal dPrice1, decimal dPrice2, decimal dPrice3, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddCustPriceD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Pm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Pm_no"].Value = sPm_no;
        com.Parameters.Add("@Im_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_id"].Value = sIm_id;
        com.Parameters.Add("@Price1", SqlDbType.Decimal, 28);
        com.Parameters["@Price1"].Value = dPrice1;
        com.Parameters.Add("@Price2", SqlDbType.Decimal, 28);
        com.Parameters["@Price2"].Value = dPrice2;
        com.Parameters.Add("@Price3", SqlDbType.Decimal, 28);
        com.Parameters["@Price3"].Value = dPrice3;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废客户价格信息
    /// </summary>
    /// <param>客户价格编号</param>
    /// <param name="Is_Visible">作废状态1为临时作废，－1为彻底作废</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteCustPrice(string sPm_no, int iIs_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteCustPrice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Pm_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Pm_no"].Value = sPm_no;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = iIs_Visible;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废客户价格信息
    /// </summary>
    /// <param>客户价格编号</param>
    /// <param name="Is_Visible">作废状态1为临时作废，－1为彻底作废</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteChildCustPrice(string sPm_no, string sChildIm_no, int iIs_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteChildCustPrice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Pm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Pm_no"].Value = sPm_no;
        com.Parameters.Add("@Im_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_id"].Value = sChildIm_no;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = iIs_Visible;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断是否可以确认：明细表有记录则可以确认，反之不可以确认
    /// </summary>
    /// <param name="Tm_No">单号</param>
    /// <param name="State"></param>
    /// <param name="Auditor">操作员</param>
    /// <returns>操作是否成功</returns>
    public string WhetherAuditCustPrice(string sPm_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_WhetherCustPriceM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Pm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Pm_no"].Value = sPm_No;

        try
        {
            con.Open();
            string strReturn = (string)com.ExecuteScalar();
            return strReturn;
        }
        catch
        {
            return null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 确认客户价格信息
    /// </summary>
    /// <param>客户价格编号</param>
    /// <param name="AuditStat">确认状态</param>
    /// <param name="Auditor">确认人</param>
    /// <returns>返回确认是否成功</returns>
    public Boolean AuditStatCustPrice(string ID, int AuditStat, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditCustPrice", con);//Pro_AuditStatCustPrice

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Pm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Pm_no"].Value = ID;
        com.Parameters.Add("@is_audit", SqlDbType.Int);
        com.Parameters["@is_audit"].Value = AuditStat;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改客户价格信息
    /// </summary>
    /// <param>客户价格编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    ///ctbRs0.Text.Trim(),ctsbRsCt_id.Text.Trim(),ctbRemark.Text.Trim()
    public Boolean UpDateCustPrice(string sPm_no, string sCt_id, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpDateCustPrice", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Pm_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Pm_no"].Value = sPm_no;
        com.Parameters.Add("@Ct_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Ct_id"].Value = sCt_id;        
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }   


    /// <summary>
    /// 修改客户价格明细信息
    /// </summary>
    /// <param>客户价格编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    ///strPm_No, ctsbProductId.Text.Trim(), ctbPrice1, ctbPrice2, ctbPrice3,ctbRemark.Text.Trim()
    public Boolean UpDateCustPriceD(string sPm_no, string sIm_id, decimal sPrice1, decimal sPrice2, decimal sPrice3, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpDateCustPriceD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Pm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Pm_no"].Value = sPm_no;
        com.Parameters.Add("@Im_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_id"].Value = sIm_id;
        com.Parameters.Add("@Price1", SqlDbType.Decimal, 28);
        com.Parameters["@Price1"].Value = sPrice1;
        com.Parameters.Add("@Price2", SqlDbType.Decimal, 28);
        com.Parameters["@Price2"].Value = sPrice2;
        com.Parameters.Add("@Price3", SqlDbType.Decimal, 28);
        com.Parameters["@Price3"].Value = sPrice3;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
}
